﻿using Dapper;
using Domain.Entity;
using Domain.Entity.ic;
using Domain.Entity.In;
using Domain.Entity.tr;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class Check_hRepository : ICheck_hRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";


        //添加盘点单
        public async Task<int> AddCheck_h(Check_h check_H)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"insert into [dbo].[ic_check_h](ic_code, ic_type, start_time, end_time, wh_id, wh_name, ic_user, ic_status, instock_status, outstock_status, bookinventory, bookamount, check_num, checkprofit_num, checklosses_num, checkprofit_total, checklosses_total, create_name, create_time,remark)
                                values('{check_H.ic_code}','{check_H.ic_type}','{check_H.start_time}','{check_H.end_time}',{check_H.wh_id},'{check_H.wh_name}','{check_H.ic_user}',{check_H.ic_status},{check_H.instock_status},{check_H.outstock_status},{check_H.bookinventory},'{check_H.bookamount}',{check_H.check_num},{check_H.checkprofit_num},{check_H.checklosses_num},'{check_H.checkprofit_total}','{check_H.checklosses_total}','{check_H.create_name}','{check_H.create_time}','{check_H.remark}');
                                SELECT CAST(SCOPE_IDENTITY() AS INT);";
                return await sqlConnection.ExecuteScalarAsync<int>(sql);//返回主键id
            }
        }


        //删除盘点单
        public async Task<int> DeleteCheck_h(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ic_check_h] set is_delete=1 where ic_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //高级查询
        public async Task<FenYe<Check_h>> GetAdvancedCheck_h(FenYe<Check_h> fenYe, string? code, string? startdate, string? enddate, int? status, string? type, int? whid, string? createname, string? startcreatetime, string? endcreatetime, string? approvername, string? startapprovertime, string? endapprovertime, string? inuser)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "create_time DESC");
                param.Add("@tabelName", "vw_check");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(code))
                    sqlparam += $" and ic_code like '%{code}%'";
                if (!string.IsNullOrEmpty(startdate))
                    sqlparam += $" and  start_time like '%{startdate}%'";
                if (!string.IsNullOrEmpty(enddate))
                    sqlparam += $" and  end_time like '%{enddate}%'";
                if (status != null && status != 0)
                    sqlparam += $" and  ic_status={status}";
                if (!string.IsNullOrEmpty(type))
                    sqlparam += $" and ic_type like '%{type}%'";
                if (whid != null && whid != 0)
                    sqlparam += $" and  wh_id={whid}";
                if (!string.IsNullOrEmpty(createname))
                    sqlparam += $" and create_name like '%{createname}%'";
                if (!string.IsNullOrEmpty(startcreatetime) || !string.IsNullOrEmpty(endcreatetime))
                    sqlparam += $" and  create_time between '{startcreatetime}' and '{endcreatetime}'";
                if (!string.IsNullOrEmpty(approvername))
                    sqlparam += $" and approver_name like '%{approvername}%'";
                if (!string.IsNullOrEmpty(startapprovertime) || !string.IsNullOrEmpty(endapprovertime))
                    sqlparam += $" and  approver_time between '{startapprovertime}' and '{endapprovertime}'";
                if (!string.IsNullOrEmpty(inuser))
                    sqlparam += $" and ic_user like '%{inuser}%'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<Check_h> list = sqlConnection.Query<Check_h>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }

        public async Task<List<Check_h>> GetByPid(int procid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select h.* from [dbo].[ic_check_h] h
                                inner join [dbo].[ic_check_d] d on h.ic_id=d.ic_id
                                where proc_id={procid}";
                return sqlConnection.Query<Check_h>(sql).ToList();
            }
        }


        //分页查询
        public async Task<FenYe<Check_h>> GetCheck_h(FenYe<Check_h> fenYe, string? code, string? type, int? status, int? whid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "create_time DESC");
                param.Add("@tabelName", "vw_check");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(code))
                    sqlparam += $" and ic_code like '%{code}%'";
                if (!string.IsNullOrEmpty(type))
                    sqlparam += $" and ic_type like '%{type}%'";
                if (status != null && status != 0)
                    sqlparam += $" and  ic_status={status}";
                if (whid != null && whid != 0)
                    sqlparam += $" and  wh_id={whid}";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<Check_h> list = sqlConnection.Query<Check_h>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }


        //根据id查询盘点信息
        public async Task<Check_h> GetCheck_hById(int icid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[ic_check_h] where ic_id={icid}";
                return await sqlConnection.QueryFirstAsync<Check_h>(sql);
            }
        }


        //根据单号查询主键ID
        public async Task<int> Getid(string code)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select ic_id from [dbo].[ic_check_h] where ic_code='{code}'";
                return await sqlConnection.ExecuteScalarAsync<int>(sql);
            }
        }


        //查询盘亏的单据
        public async Task<List<Check_h>> Getkui()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[ic_check_h] where is_delete=0 and ic_status=2 and (outstock_status=7 or outstock_status=8) and checklosses_num>0";
                return sqlConnection.Query<Check_h>(sql).ToList();
            }
        }

        public async Task<CheckTotals> Gettotal(
            string? proc_code, string? ic_code, string? ic_type, string? create_name,
            int? type_id, string? proc_primary_unit, int? wh_id, string? ic_user,
            string? createstartTime, string? createendTime,
            decimal? checkprofitNumMin, decimal? checkprofitNumMax,
            decimal? checklossesNumMin, decimal? checklossesNumMax,
            decimal? checkprofitTotalMin, decimal? checkprofitTotalMax,
            decimal? checklossesTotalMin, decimal? checklossesTotalMax,
            int[]? icStatusArray)
        {
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(con))
                {
                    await sqlConnection.OpenAsync();

                    // 1. 基础 SELECT 语句
                    string baseSql = @"
                    SELECT 
                        COUNT(DISTINCT vcd.ic_code) AS CheckCount,
                        ISNULL(SUM(CASE WHEN vcd.checkprofit_num > 0 THEN vcd.checkprofit_num ELSE 0 END), 0) AS CheckProfitNum,
                        ISNULL(SUM(CASE WHEN vcd.checklosses_num > 0 THEN vcd.checklosses_num ELSE 0 END), 0) AS CheckLossesNum,
                        ISNULL(SUM(CASE WHEN vcd.checkprofit_total > 0 THEN vcd.checkprofit_total ELSE 0 END), 0) AS CheckProfitTotal,
                        ISNULL(SUM(CASE WHEN vcd.checklosses_total > 0 THEN vcd.checklosses_total ELSE 0 END), 0) AS CheckLossesTotal
                    FROM [dbo].[vw_check_detail] vcd";

                    // 2. 构建 WHERE 子句
                    var whereBuilder = new StringBuilder(" WHERE header_is_delete = 0 AND product_is_delete = 0"); // 注意开头有空格

                    // 1. 产品编码/名称条件
                    if (!string.IsNullOrEmpty(proc_code))
                    {
                        // 注意：这里极易受 SQL 注入攻击，应使用参数化查询
                        whereBuilder.Append($" AND (proc_name LIKE '%{proc_code}%' OR proc_code LIKE '%{proc_code}%')");
                    }

                    // 2. 盘点单号条件
                    if (!string.IsNullOrEmpty(ic_code))
                    {
                        whereBuilder.Append($" AND ic_code LIKE '%{ic_code}%'");
                    }

                    // 3. 盘点类型条件
                    if (!string.IsNullOrEmpty(ic_type))
                    {
                        whereBuilder.Append($" AND ic_type LIKE '%{ic_type}%'");
                    }

                    // 4. 创建人条件
                    if (!string.IsNullOrEmpty(create_name))
                    {
                        whereBuilder.Append($" AND create_name LIKE '%{create_name}%'");
                    }

                    // 5. 商品分类条件
                    if (type_id.HasValue)
                    {
                        whereBuilder.Append($" AND type_id = {type_id.Value}");
                    }

                    // 6. 主单位条件
                    if (!string.IsNullOrEmpty(proc_primary_unit))
                    {
                        whereBuilder.Append($" AND proc_primary_unit LIKE '%{proc_primary_unit}%'");
                    }

                    // 7. 仓库ID条件
                    if (wh_id.HasValue)
                    {
                        whereBuilder.Append($" AND wh_id = {wh_id.Value}");
                    }

                    // 8. 盘点人条件
                    if (!string.IsNullOrEmpty(ic_user))
                    {
                        whereBuilder.Append($" AND ic_user LIKE '%{ic_user}%'");
                    }

                    // 9. 创建时间范围条件
                    if (!string.IsNullOrEmpty(createstartTime) && !string.IsNullOrEmpty(createendTime))
                    {
                        // 注意：日期格式化和注入风险
                        whereBuilder.Append($" AND create_time BETWEEN '{createstartTime}' AND '{createendTime}'");
                    }

                    // 10. 盘盈数量范围
                    if (checkprofitNumMin.HasValue)
                    {
                        whereBuilder.Append($" AND checkprofit_num >= {checkprofitNumMin.Value}");
                    }
                    if (checkprofitNumMax.HasValue)
                    {
                        whereBuilder.Append($" AND checkprofit_num <= {checkprofitNumMax.Value}");
                    }

                    // 11. 盘亏数量范围
                    if (checklossesNumMin.HasValue)
                    {
                        whereBuilder.Append($" AND checklosses_num >= {checklossesNumMin.Value}");
                    }
                    if (checklossesNumMax.HasValue)
                    {
                        whereBuilder.Append($" AND checklosses_num <= {checklossesNumMax.Value}");
                    }

                    // 12. 盘盈金额范围
                    if (checkprofitTotalMin.HasValue)
                    {
                        whereBuilder.Append($" AND checkprofit_total >= {checkprofitTotalMin.Value}");
                    }
                    if (checkprofitTotalMax.HasValue)
                    {
                        whereBuilder.Append($" AND checkprofit_total <= {checkprofitTotalMax.Value}");
                    }

                    // 13. 盘亏金额范围
                    if (checklossesTotalMin.HasValue)
                    {
                        whereBuilder.Append($" AND checklosses_total >= {checklossesTotalMin.Value}");
                    }
                    if (checklossesTotalMax.HasValue)
                    {
                        whereBuilder.Append($" AND checklosses_total <= {checklossesTotalMax.Value}");
                    }

                    // 14. 盘点结果状态条件
                    if (icStatusArray != null && icStatusArray.Length > 0)
                    {
                        // 注意：数组值直接拼接，存在注入风险
                        whereBuilder.Append($" AND ic_status IN ({string.Join(",", icStatusArray)})");
                    }

                    // ---- 关键修改：将 WHERE 子句拼接到基础 SQL 上 ----
                    string finalSql = baseSql + whereBuilder.ToString();
                    // ----------------------------------------------------

                    // 3. 执行查询
                    using (SqlCommand command = new SqlCommand(finalSql, sqlConnection)) // 使用拼接后的 finalSql
                    {
                        // 注意：当前代码没有添加任何 SqlParameter，这使得上面的字符串拼接非常危险。
                        // 理想情况下，所有用户输入都应该作为参数传递。
                        using (SqlDataReader reader = await command.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())
                            {
                                return new CheckTotals
                                {
                                    CheckCount = reader.GetInt32("CheckCount"),
                                    CheckProfitNum = reader.GetInt32("CheckProfitNum"), // 确认数据库返回的是 INT，否则用 GetDecimal
                                    CheckLossesNum = reader.GetInt32("CheckLossesNum"), // 确认数据库返回的是 INT，否则用 GetDecimal
                                    CheckProfitTotal = reader.GetDecimal("CheckProfitTotal"),
                                    CheckLossesTotal = reader.GetDecimal("CheckLossesTotal")
                                };
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                // 记录日志
                throw new Exception("获取盘点汇总数据失败", ex);
            }

            return new CheckTotals(); // 如果没有数据返回，返回默认值
        }


        //查询盘盈的单据
        public async Task<List<Check_h>> Getying()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[ic_check_h] where is_delete=0 and ic_status=2 and (instock_status=4 or instock_status=5) and checkprofit_num>0";
                return sqlConnection.Query<Check_h>(sql).ToList();
            }
        }


        //修改盘点单
        public async Task<int> UpdateCheck_h(Check_h check_H)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ic_check_h] set ic_type='{check_H.ic_type}', start_time='{check_H.start_time}', end_time='{check_H.end_time}', wh_id={check_H.wh_id}, wh_name='{check_H.wh_name}', ic_user='{check_H.ic_user}', 
                                ic_status={check_H.ic_status}, instock_status={check_H.instock_status}, outstock_status={check_H.outstock_status}, bookinventory={check_H.bookinventory}, bookamount='{check_H.bookamount}', check_num={check_H.check_num},
                                checkprofit_num={check_H.checkprofit_num}, checklosses_num={check_H.checklosses_num}, checkprofit_total='{check_H.checkprofit_total}', checklosses_total='{check_H.checklosses_total}', update_name='{check_H.update_name}', update_time='{check_H.update_time}', remark='{check_H.remark}' where ic_id={check_H.ic_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //修改出入库状态
        public async Task<int> UpdateStatus(string name, int status, int ic_id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ic_check_h] set {name}={status} where ic_id={ic_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }
    }
}
